/**
 * 
 */
package dbAccess;

import java.sql.*;
import java.util.*;

import exceptions.*;

import model.Commande_prof;

/**
 * @author Anthares & Hennek
 *
 */
public class CommandeProfDBAccess {
	public ArrayList<Commande_prof> getAllCommandeProf() throws PasIdentificationException, DatabaseErrorException{
		ArrayList<Commande_prof> tabCom = new ArrayList<Commande_prof>();
		SyllabusDBAccess sdba = new SyllabusDBAccess();
		MembreComiteDBAccess mcdba = new MembreComiteDBAccess();
		ProfesseurDBAccess pdba = new ProfesseurDBAccess();
		GregorianCalendar gc;
		Connection conn = DBConnexionSingleton.getInstance();
		PreparedStatement stat;
		String query;
		try {
			query = "select * from commande_prof;";
			stat = conn.prepareStatement(query);
			ResultSet data = stat.executeQuery();
			while(data.next()){
				java.sql.Date datCom = data.getDate("date_commande_prof");
				gc = new GregorianCalendar();
				gc.setTime(datCom);
				Commande_prof com = new Commande_prof(data.getInt("commande_prof_id"), gc, sdba.getSyllabus(data.getInt("syllabus_id")), mcdba.getMembreComite(data.getInt("membre_comite_id")), data.getString("etat"), data.getBoolean("paye"), data.getInt("quantite"), pdba.getProfesseur(data.getInt("professeur_id")));
				
				java.sql.Date datLiv = data.getDate("date_livraison_prof");
				if(data.wasNull()==false){
					gc = new GregorianCalendar();
					gc.setTime(datLiv);
					com.setDate_livraison(gc);
				}
				
				String comment = data.getString("commentaires");
				if(data.wasNull()==false) com.setCommentaires(comment);
				
				tabCom.add(com);
			}
			stat.close();
		} catch (SQLException e) {
			throw new DatabaseErrorException(e);
		}
		return tabCom;
	}
	public int addCommandeProf(Commande_prof com) throws PasIdentificationException, DatabaseErrorException{
		int com_id = -1;
		Connection conn = DBConnexionSingleton.getInstance();
		PreparedStatement stat;
		String query;
		try {
			query = "select max(commande_prof_id) from commande_prof;";
			stat = conn.prepareStatement(query);
			ResultSet data = stat.executeQuery();
			while(data.next()){
				com_id = data.getInt(1);
			}
			com_id++;
			stat.close();
			
			query = "insert into commande_prof (date_commande_prof, syllabus_id, membre_comite_id, etat, paye, quantite, professeur_id) values (?,?,?,?,?,?,?);";
			stat = conn.prepareStatement(query);
				stat.setDate(1, new java.sql.Date(com.getDate_commande().getTimeInMillis()));
				stat.setInt(2, com.getContenu().getSyllabus_id());
				stat.setInt(3, com.getCreateur().getId());
				stat.setString(4, com.getEtat());
				stat.setBoolean(5, com.isPaye());
				stat.setInt(6, com.getQuantite());
				stat.setInt(7, com.getClient().getId());
			stat.executeUpdate();
			stat.close();
			
			if(com.getDate_livraison()!=null){
				query = "update into commande_prof set date_livraison_prof = ? where commande_prof_id = ?;";
				stat = conn.prepareStatement(query);
					stat.setDate(1, new java.sql.Date(com.getDate_livraison().getTimeInMillis()));
					stat.setInt(2, com_id);
				stat.executeUpdate();
				stat.close();
			}
			
			if(com.getCommentaires()!=null){
				query = "update into commande_prof set commentaires = ? where commande_prof_id = ?;";
				stat = conn.prepareStatement(query);
					stat.setString(1, com.getCommentaires());
					stat.setInt(2, com_id);
				stat.executeUpdate();
				stat.close();
			}
			conn.commit();
		} catch (SQLException e) {
			throw new DatabaseErrorException(e);
		}
		return com_id;
	}
}
